package com.myersinfosys.protrack.poi;

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Map;
import java.util.HashMap;
import java.io.IOException;

public class ExcelWeeklyGrid
{
    private static final String[] days = {
        "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"};
    private static final String[] abbreviatedDays = {
        "SUN.", "MON.", "TUES.", "WED.", "THURS.", "FRI.", "SAT."};
    
    SimpleDateFormat date_format = new SimpleDateFormat("M/d/yy"); // 7/6/11
    SimpleDateFormat time_format = new SimpleDateFormat("h:mma");  // 10:00pm

    private static final String[]  months = {
        "January", "February", "March","April", "May", "June","July", "August",
        "September","October", "November", "December"};
    
   private Calendar calendar;
   private Workbook wb;
   private Boolean isXlsx;
   private Map<String, CellStyle> styles;
   private String filename;

   public ExcelWeeklyGrid(String filename) {
	   
	   this.calendar = Calendar.getInstance();
	   
	   this.isXlsx = filename.matches("(?i).*.xlsx");
	   this.wb = isXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
	   this.filename = filename;
	   
	   this.styles = createStyles(this.wb);
   }

   public int setLayout(int startYear, int startMonth, int startDay,
		                int nWeeks, int nDaysPerWeek, 
		                int startTime, int endTime, int deltaTime)
   {
	   String title = "NHL NETWORK USA MASTER SCHEDULE";
	   //Calendar today = Calendar.getInstance();
	   int success = 0;
	   
       if (startYear < 0) 
    	   startYear = calendar.get(Calendar.YEAR);
       
       if (startMonth < 1 || startMonth > 12)
    	   startMonth = calendar.get(Calendar.MONTH);
       else
    	   --startMonth;  //make it zero-based
       
       if(startDay < 1 || startDay > 27)
    	   startDay = 1;
       
       if (nWeeks < 1 || nWeeks > 52)
    	   nWeeks = 1;
       
       if(nDaysPerWeek < 0 || nDaysPerWeek > 7)
    	   nDaysPerWeek = 7;
       
	   calendar.set(Calendar.YEAR, startYear);
       calendar.set(Calendar.MONTH, startMonth);
       calendar.set(Calendar.DAY_OF_MONTH, startDay);       
       int startDow = calendar.get(Calendar.DAY_OF_WEEK)-1;
       calendar.setFirstDayOfWeek(startDow);
       
       for (int weekIndex = 0; weekIndex < nWeeks; weekIndex++) {
 
           //create a sheet for each week
           Sheet sheet = wb.createSheet("Week"+String.format("%02d", weekIndex+1));
           
           
           sheet.setMargin(Sheet.TopMargin, 0.3);
           sheet.setMargin(Sheet.RightMargin, 0.3);
           sheet.setMargin(Sheet.BottomMargin, 0.3);
           sheet.setMargin(Sheet.LeftMargin, 0.3);
           sheet.setDisplayGridlines(false);
           sheet.setPrintGridlines(false);
           sheet.setFitToPage(true);
           sheet.setZoom(1, 0);
           sheet.setHorizontallyCenter(true);
           //sheet.setVerticallyCenter(true);
           PrintSetup printSetup = sheet.getPrintSetup();
           printSetup.setLandscape(true);
           printSetup.setHeaderMargin(0.0);
           printSetup.setFooterMargin(0.0);

           //the following three statements are required only for HSSF
           sheet.setAutobreaks(true);
           printSetup.setScale((short)100);
           //printSetup.setFitHeight((short)1);
           //printSetup.setFitWidth((short)1);

           //the header row: center left text in 12pt font
           Row headerRow = sheet.createRow(0);
           headerRow.setHeightInPoints(16);

           
           Cell titleCell = headerRow.createCell(1);
           
           String start_date = date_format.format(calendar.getTime());
           Calendar end = Calendar.getInstance();
           end.setTime(calendar.getTime());
           end.add(Calendar.DATE, 6);
           String end_date = date_format.format(end.getTime());
           title = title + " - (" + start_date +"-"+end_date+")";
           
           titleCell.setCellValue(title);
           titleCell.setCellStyle(styles.get("title"));
           sheet.addMergedRegion(CellRangeAddress.valueOf("$B$1:$H$1"));

           //header with day titles
           Row dayRow = sheet.createRow(1);
           dayRow.setHeightInPoints(12);
           Cell tzCell = dayRow.createCell(0);
           tzCell.setCellValue("(EST)");  
           tzCell.setCellStyle(styles.get("time"));
           tzCell = dayRow.createCell(8);
           tzCell.setCellValue("(EST)");  
           tzCell.setCellStyle(styles.get("time"));
           
           
           // day column headers
           for (int i = 0; i < days.length; i++) {
               //set column widths, the width is measured in units of 1/256th of a character width
               sheet.setColumnWidth(i + 1, 17*256); //the column is 17 characters wide
               //sheet.addMergedRegion(new CellRangeAddress(1, 1, i*2+1, i*2+2));
               Cell dayCell = dayRow.createCell(i+1);
               int dowIndex = startDow + i;
               if (dowIndex >= days.length)
            	   dowIndex -= days.length; 
               dayCell.setCellValue(abbreviatedDays[dowIndex]+" - "+(calendar.get(Calendar.MONTH)+1)+"/"+calendar.get(Calendar.DAY_OF_MONTH));
               dayCell.setCellStyle(styles.get("days"));
               calendar.add(Calendar.DATE, 1);
           }
           
           int hrs = 0;
           int min = 0;
           String am_pm;
           String time_str;
           
           int rowIndex = 1;

           sheet.setColumnWidth(0, 7*256);
           sheet.setColumnWidth(8, 7*256);
           
           for (int t=startTime; t<endTime; t+=deltaTime) {
        	   
        	   hrs = t/3600;
        	   min = (t - (hrs*3600))/60;
        	   am_pm = (hrs >= 12 && hrs < 24) ? "pm" : "am";
        	   
        	   if(hrs > 24) 
        		   hrs-=24;
        	   else
        		   if(hrs > 12) 
        			   hrs-=12;
        	   
        	   time_str = hrs + ":" + String.format("%02d", min) + am_pm;
        	   
        	   Row timeRow = sheet.createRow(++rowIndex);
        	   timeRow.setHeightInPoints(10);
        	   Cell ltimeCell = timeRow.createCell(0);
        	   Cell rtimeCell = timeRow.createCell(8);
        	   
        	   if(min == 0) {
        		   ltimeCell.setCellValue(time_str);
        		   rtimeCell.setCellValue(time_str);
        	   }
               ltimeCell.setCellStyle(styles.get("time"));
               rtimeCell.setCellStyle(styles.get("time"));
               
           }
       }

       // Write the output to a file
	try {
		FileOutputStream out = new FileOutputStream(filename);
	     wb.write(out);
	     out.close();
	     success = 1;
	} catch (IOException e) {
		e.printStackTrace();
		success= 0;
	}
  
	return success;
   }

   /**
    * cell styles used for formatting calendar sheets
    */
   private static Map<String, CellStyle> createStyles(Workbook wb){
       Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

       short borderColor = IndexedColors.GREY_50_PERCENT.getIndex();
       short lightBorderColor = IndexedColors.GREY_25_PERCENT.getIndex();
       short dayColor = IndexedColors.GREY_25_PERCENT.getIndex();
       short whiteColor = IndexedColors.WHITE.getIndex();
       short blackColor = IndexedColors.BLACK.getIndex();
       short weekendColor;
       
       
       weekendColor = IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex();
       //short weekendColor = Color.BLUE.;
       //weekendColor = (new XSSFColor(new java.awt.Color(255, 255, 128)))..getIndexed();
       
       final short def = -1;
       
       PoiExcelCellStyles cellStyles = new PoiExcelCellStyles(wb);
       
       // create the fonts
       Font titleFont = cellStyles.createCustomFontStyle(null, 
    		   (short)10, 
    		   blackColor, 
				true, 
				false,
				false);
       Font dayFont = cellStyles.createCustomFontStyle(null, 
    		   (short)6, 
    		   blackColor, 
				true, 
				false,
				false);
       Font timeFont = cellStyles.createCustomFontStyle(null, 
    		   (short)6, 
    		   blackColor, 
				false, 
				false,
				false);
       Font cellFont = cellStyles.createCustomFontStyle(null, 
    		   (short)6, 
    		   blackColor, 
				true, 
				false,
				true);       
       
       
       CellStyle style = cellStyles.createCustomCellStyle(titleFont, 
			   def,def,def,def,
			   def,def,def,def,
			   def,
			   def,
			   def,
			   CellStyle.ALIGN_LEFT,
			   CellStyle.VERTICAL_CENTER,
			   false);
       styles.put("title", style);
       
       

       style = cellStyles.createCustomCellStyle(dayFont, 
    		   CellStyle.BORDER_THIN,CellStyle.BORDER_THIN,CellStyle.BORDER_THIN,CellStyle.BORDER_THIN,
    		   borderColor,borderColor,borderColor,borderColor,
			   def,
			   dayColor,
			   CellStyle.SOLID_FOREGROUND,
			   CellStyle.ALIGN_CENTER,
			   CellStyle.VERTICAL_CENTER,
			   false);       
       styles.put("days", style);
       
       style = cellStyles.createCustomCellStyle(timeFont, 
    		   CellStyle.BORDER_THIN,CellStyle.BORDER_THIN,CellStyle.BORDER_THIN,CellStyle.BORDER_THIN,
    		   borderColor,borderColor,borderColor,borderColor,
			   def,
			   whiteColor,
			   CellStyle.SOLID_FOREGROUND,
			   CellStyle.ALIGN_LEFT,
			   CellStyle.VERTICAL_CENTER,
			   false);       
       styles.put("time", style);       

       
       style = cellStyles.createCustomCellStyle(dayFont, 
    		   def,def,CellStyle.BORDER_THIN,CellStyle.BORDER_THIN,
			   def,def,borderColor,borderColor,
			   def,
			   weekendColor,
			   CellStyle.SOLID_FOREGROUND,
			   CellStyle.ALIGN_LEFT,
			   CellStyle.VERTICAL_TOP,
			   false);              
       styles.put("weekend_left", style);
       

       style = cellStyles.createCustomCellStyle(dayFont, 
    		   def,CellStyle.BORDER_THIN,CellStyle.BORDER_THIN,def,
			   def,borderColor,borderColor,def,
			   def,
			   weekendColor,
			   CellStyle.SOLID_FOREGROUND,
			   CellStyle.ALIGN_CENTER,
			   CellStyle.VERTICAL_TOP,
			   false);                     
       styles.put("weekend_right", style);

       
       style = cellStyles.createCustomCellStyle(dayFont, 
    		   def,def,CellStyle.BORDER_THIN,CellStyle.BORDER_THIN,
			   def,def,borderColor,borderColor,
			   def,
			   whiteColor,
			   CellStyle.SOLID_FOREGROUND,
			   CellStyle.ALIGN_LEFT,
			   CellStyle.VERTICAL_TOP,
			   false);       
       styles.put("workday_left", style);
       
       
       style = cellStyles.createCustomCellStyle(dayFont, 
    		   def,CellStyle.BORDER_THIN,CellStyle.BORDER_THIN,def,
			   def,borderColor,borderColor,def,
			   def,
			   whiteColor,
			   CellStyle.SOLID_FOREGROUND,
			   CellStyle.ALIGN_CENTER,
			   CellStyle.VERTICAL_TOP,
			   false);       
       styles.put("workday_right", style);


       style = cellStyles.createCustomCellStyle(dayFont, 
    		   def,def,CellStyle.BORDER_THIN,CellStyle.BORDER_THIN,
			   def,def,borderColor,def,
			   def,
			   lightBorderColor,
			   CellStyle.SOLID_FOREGROUND,
			   def,
			   def,
			   false);       
       styles.put("grey_left", style);


       style = cellStyles.createCustomCellStyle(dayFont, 
    		   def,CellStyle.BORDER_THIN,CellStyle.BORDER_THIN,def,
			   def,borderColor,borderColor,def,
			   def,
			   lightBorderColor,
			   CellStyle.SOLID_FOREGROUND,
			   def,
			   def,
			   false);       
       styles.put("grey_right", style);

       return styles;
   }
 }